--부서
--부서 NO(department_no) : number(3) primary key
--부서명(department_name) : varchar2(50) not null
--위치(location) : varchar2(100) nullable

drop table department cascade constraint;
select * from department;
select * from tab;
select * from employee;
drop table employee cascade constraint;

-- 테이블 생성
create table department(
	department_no number(3) constraint department_pk primary key,
	department_name varchar2(50) not null,
	location varchar2(100) 
);

insert into department values(1, '기획부', '서울');
insert into department values(2, '인사부', '부산');
insert into department values(3, '개발부', '서울');
insert into department values(4, '기획부', '부산');
insert into department values(5, '인사부', '서울');

select * from department order by location
-- department_no에 넣어줄 숫자를 생성해줄 Sequence객체 생성

drop sequence department_seq;

create sequence department_seq
nocache;

select department_seq.nextval from dual;

--직원(employee)
--직원_id(employee_id) varchar2(6) primary key
--직원_이름(employee_name) varchar2(50) not null
--email 주소(email) varchar2(50) not null
--직책(job_position) varchar2(50) not null
--연봉(salary) number(12) not null
--입사일(hire_date) date not null
--소속부서(department_no) number(3) nullable 부서테이블의 pk를 참조하는 외래키
--부서테이블(department)

create table employee(
	employee_id varchar2(6) constraint employee_pk primary key,
	employee_name varchar2(50) not null,
	email varchar2(50) not null,
	job_position varchar2(50)  not null,
	salary number(12) not null,
	hire_date date not null,
	department_no number(3),
	constraint employee_fk foreign key(department_no) references department(department_no) on delete cascade
	--on delete cascade 참조하는것을 지우고 싶을때 자식이 부모꺼를 참조하는데
	--부모꺼 지우려면 원래 안지워짐 근데 부모꺼 지울라고 이때 저거 쓰면
	--자식들하고 부모 다 지워짐	
);

drop table employee cascade constraint;
select * from employee;

insert into employee values('e-1', '오인선', '111@aaa', '회장', '1000000000', '2014_09_19', 1);
insert into employee values('e-2', '정상길', '222@aaa', '사장', '1000000000', '2014_09_19', 1);
insert into employee values('e-3', '방지선', '333@aaa', '과장','1000000000', '2014_09_19', 1);





select dept.department_no, dept.DEPARTMENT_NAME, dept.location,
		emp.employee_id, emp.employee_name, emp.email
from department dept, employee emp
where dept.DEPARTMENT_NO=1
and dept.department_no=emp.department_no
; 

select * from employee whewre department_no=1;





